{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Train a Deep NN to predict Asset Price movements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup Docker for GPU acceleration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`docker run -it -p 8889:8888 -v /path/to/machine-learning-for-trading/16_convolutions_neural_nets/cnn:/cnn --name tensorflow tensorflow/tensorflow:latest-gpu-py3 bash`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports & Settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Using TensorFlow backend.\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "from pathlib import Path\n",
    "from importlib import reload\n",
    "from joblib import dump, load\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.model_selection import train_test_split, GridSearchCV, StratifiedKFold\n",
    "from sklearn.metrics import roc_auc_score\n",
    "\n",
    "import tensorflow as tf\n",
    "from keras.models import Sequential\n",
    "from keras import backend as K\n",
    "from keras.wrappers.scikit_learn import KerasClassifier\n",
    "from keras.layers import Dense, Dropout, Activation\n",
    "from keras.models import load_model\n",
    "from keras.callbacks import Callback, EarlyStopping, TensorBoard, ModelCheckpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(42)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load the Quandl adjusted stock price data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 2896 entries, 2007-01-01 to 2018-03-27\n",
      "Columns: 3199 entries, A to ZUMZ\n",
      "dtypes: float64(3199)\n",
      "memory usage: 70.7 MB\n"
     ]
    }
   ],
   "source": [
    "prices = (pd.read_hdf('../data/assets.h5', 'quandl/wiki/prices')\n",
    "          .adj_close\n",
    "          .unstack().loc['2007':])\n",
    "prices.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resample to weekly frequency"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start by generating weekly returns for close to 2,500 stocks without missing data for the 2008-17 period, as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 522 entries, 2017-12-31 to 2008-01-06\n",
      "Freq: -1W-SUN\n",
      "Columns: 2489 entries, A to ZUMZ\n",
      "dtypes: float64(2489)\n",
      "memory usage: 9.9 MB\n"
     ]
    }
   ],
   "source": [
    "returns = (prices\n",
    "           .resample('W')\n",
    "           .last()\n",
    "           .pct_change()\n",
    "           .loc['2008': '2017']\n",
    "           .dropna(axis=1)\n",
    "           .sort_index(ascending=False))\n",
    "returns.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>A</th>\n",
       "      <th>AAL</th>\n",
       "      <th>AAN</th>\n",
       "      <th>AAON</th>\n",
       "      <th>AAP</th>\n",
       "      <th>AAPL</th>\n",
       "      <th>AAWW</th>\n",
       "      <th>ABAX</th>\n",
       "      <th>ABC</th>\n",
       "      <th>ABCB</th>\n",
       "      <th>...</th>\n",
       "      <th>ZEUS</th>\n",
       "      <th>ZIGO</th>\n",
       "      <th>ZINC</th>\n",
       "      <th>ZION</th>\n",
       "      <th>ZIOP</th>\n",
       "      <th>ZIXI</th>\n",
       "      <th>ZLC</th>\n",
       "      <th>ZMH</th>\n",
       "      <th>ZQK</th>\n",
       "      <th>ZUMZ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>-0.005642</td>\n",
       "      <td>-0.010648</td>\n",
       "      <td>-0.010184</td>\n",
       "      <td>-0.001361</td>\n",
       "      <td>-0.008553</td>\n",
       "      <td>-0.033027</td>\n",
       "      <td>-0.024938</td>\n",
       "      <td>-0.001814</td>\n",
       "      <td>-0.006922</td>\n",
       "      <td>-0.019329</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.029797</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.009741</td>\n",
       "      <td>0.022222</td>\n",
       "      <td>-0.015730</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.029138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-24</th>\n",
       "      <td>-0.003846</td>\n",
       "      <td>0.029965</td>\n",
       "      <td>0.090171</td>\n",
       "      <td>0.044034</td>\n",
       "      <td>-0.001490</td>\n",
       "      <td>0.006557</td>\n",
       "      <td>0.046087</td>\n",
       "      <td>0.032681</td>\n",
       "      <td>-0.007620</td>\n",
       "      <td>0.017598</td>\n",
       "      <td>...</td>\n",
       "      <td>0.032153</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.026395</td>\n",
       "      <td>-0.068966</td>\n",
       "      <td>-0.024123</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.067164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-17</th>\n",
       "      <td>0.003413</td>\n",
       "      <td>0.000784</td>\n",
       "      <td>-0.052591</td>\n",
       "      <td>-0.014006</td>\n",
       "      <td>0.003888</td>\n",
       "      <td>0.026569</td>\n",
       "      <td>0.004367</td>\n",
       "      <td>0.008396</td>\n",
       "      <td>0.074625</td>\n",
       "      <td>0.026567</td>\n",
       "      <td>...</td>\n",
       "      <td>0.036715</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.018064</td>\n",
       "      <td>-0.018059</td>\n",
       "      <td>0.075472</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.051887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-10</th>\n",
       "      <td>-0.019071</td>\n",
       "      <td>0.041012</td>\n",
       "      <td>-0.005359</td>\n",
       "      <td>-0.017882</td>\n",
       "      <td>0.010375</td>\n",
       "      <td>-0.009822</td>\n",
       "      <td>-0.028014</td>\n",
       "      <td>-0.010386</td>\n",
       "      <td>0.020600</td>\n",
       "      <td>-0.054271</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.002410</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016973</td>\n",
       "      <td>-0.015556</td>\n",
       "      <td>-0.055679</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.062657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-03</th>\n",
       "      <td>-0.009660</td>\n",
       "      <td>0.009267</td>\n",
       "      <td>0.105501</td>\n",
       "      <td>0.013947</td>\n",
       "      <td>0.112630</td>\n",
       "      <td>-0.022404</td>\n",
       "      <td>0.073838</td>\n",
       "      <td>-0.028456</td>\n",
       "      <td>0.045796</td>\n",
       "      <td>0.024717</td>\n",
       "      <td>...</td>\n",
       "      <td>0.065742</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.080475</td>\n",
       "      <td>0.014656</td>\n",
       "      <td>-0.006637</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.047244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-02-03</th>\n",
       "      <td>0.038265</td>\n",
       "      <td>0.252238</td>\n",
       "      <td>0.002941</td>\n",
       "      <td>0.095182</td>\n",
       "      <td>0.097833</td>\n",
       "      <td>0.028767</td>\n",
       "      <td>0.006245</td>\n",
       "      <td>-0.078058</td>\n",
       "      <td>0.036913</td>\n",
       "      <td>0.083217</td>\n",
       "      <td>...</td>\n",
       "      <td>0.137066</td>\n",
       "      <td>0.127561</td>\n",
       "      <td>0.286550</td>\n",
       "      <td>0.167722</td>\n",
       "      <td>-0.087879</td>\n",
       "      <td>0.069364</td>\n",
       "      <td>0.171949</td>\n",
       "      <td>0.193189</td>\n",
       "      <td>0.127811</td>\n",
       "      <td>0.149083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-01-27</th>\n",
       "      <td>-0.013963</td>\n",
       "      <td>-0.048762</td>\n",
       "      <td>0.191310</td>\n",
       "      <td>0.071788</td>\n",
       "      <td>0.043997</td>\n",
       "      <td>-0.194286</td>\n",
       "      <td>-0.008984</td>\n",
       "      <td>-0.090807</td>\n",
       "      <td>-0.034771</td>\n",
       "      <td>0.054572</td>\n",
       "      <td>...</td>\n",
       "      <td>0.018349</td>\n",
       "      <td>-0.026292</td>\n",
       "      <td>-0.046975</td>\n",
       "      <td>0.136418</td>\n",
       "      <td>-0.003021</td>\n",
       "      <td>0.145695</td>\n",
       "      <td>0.042164</td>\n",
       "      <td>-0.014553</td>\n",
       "      <td>0.141892</td>\n",
       "      <td>0.118666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-01-20</th>\n",
       "      <td>-0.065000</td>\n",
       "      <td>0.086627</td>\n",
       "      <td>-0.080541</td>\n",
       "      <td>-0.054762</td>\n",
       "      <td>-0.007176</td>\n",
       "      <td>-0.065609</td>\n",
       "      <td>0.015818</td>\n",
       "      <td>-0.019721</td>\n",
       "      <td>-0.015219</td>\n",
       "      <td>-0.044397</td>\n",
       "      <td>...</td>\n",
       "      <td>0.040573</td>\n",
       "      <td>0.010999</td>\n",
       "      <td>-0.167109</td>\n",
       "      <td>-0.051614</td>\n",
       "      <td>-0.054286</td>\n",
       "      <td>-0.124638</td>\n",
       "      <td>0.037172</td>\n",
       "      <td>-0.037312</td>\n",
       "      <td>-0.030144</td>\n",
       "      <td>-0.076969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-01-13</th>\n",
       "      <td>0.035375</td>\n",
       "      <td>-0.041902</td>\n",
       "      <td>-0.037818</td>\n",
       "      <td>-0.046538</td>\n",
       "      <td>-0.101486</td>\n",
       "      <td>-0.040878</td>\n",
       "      <td>-0.052095</td>\n",
       "      <td>0.097385</td>\n",
       "      <td>0.080137</td>\n",
       "      <td>-0.017313</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.054176</td>\n",
       "      <td>-0.047993</td>\n",
       "      <td>-0.102381</td>\n",
       "      <td>0.037264</td>\n",
       "      <td>-0.022346</td>\n",
       "      <td>-0.172662</td>\n",
       "      <td>0.011799</td>\n",
       "      <td>0.051880</td>\n",
       "      <td>0.018692</td>\n",
       "      <td>-0.094249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-01-06</th>\n",
       "      <td>-0.072553</td>\n",
       "      <td>-0.156356</td>\n",
       "      <td>-0.068707</td>\n",
       "      <td>-0.133301</td>\n",
       "      <td>-0.065496</td>\n",
       "      <td>-0.098984</td>\n",
       "      <td>-0.029478</td>\n",
       "      <td>-0.098374</td>\n",
       "      <td>-0.037363</td>\n",
       "      <td>-0.132733</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.027290</td>\n",
       "      <td>-0.075806</td>\n",
       "      <td>-0.004739</td>\n",
       "      <td>-0.081058</td>\n",
       "      <td>0.101538</td>\n",
       "      <td>-0.143737</td>\n",
       "      <td>-0.134100</td>\n",
       "      <td>0.000752</td>\n",
       "      <td>-0.133102</td>\n",
       "      <td>-0.269012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 2489 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker             A       AAL       AAN      AAON       AAP      AAPL  \\\n",
       "date                                                                     \n",
       "2017-12-31 -0.005642 -0.010648 -0.010184 -0.001361 -0.008553 -0.033027   \n",
       "2017-12-24 -0.003846  0.029965  0.090171  0.044034 -0.001490  0.006557   \n",
       "2017-12-17  0.003413  0.000784 -0.052591 -0.014006  0.003888  0.026569   \n",
       "2017-12-10 -0.019071  0.041012 -0.005359 -0.017882  0.010375 -0.009822   \n",
       "2017-12-03 -0.009660  0.009267  0.105501  0.013947  0.112630 -0.022404   \n",
       "2008-02-03  0.038265  0.252238  0.002941  0.095182  0.097833  0.028767   \n",
       "2008-01-27 -0.013963 -0.048762  0.191310  0.071788  0.043997 -0.194286   \n",
       "2008-01-20 -0.065000  0.086627 -0.080541 -0.054762 -0.007176 -0.065609   \n",
       "2008-01-13  0.035375 -0.041902 -0.037818 -0.046538 -0.101486 -0.040878   \n",
       "2008-01-06 -0.072553 -0.156356 -0.068707 -0.133301 -0.065496 -0.098984   \n",
       "\n",
       "ticker          AAWW      ABAX       ABC      ABCB  ...      ZEUS      ZIGO  \\\n",
       "date                                                ...                       \n",
       "2017-12-31 -0.024938 -0.001814 -0.006922 -0.019329  ... -0.029797  0.000000   \n",
       "2017-12-24  0.046087  0.032681 -0.007620  0.017598  ...  0.032153  0.000000   \n",
       "2017-12-17  0.004367  0.008396  0.074625  0.026567  ...  0.036715  0.000000   \n",
       "2017-12-10 -0.028014 -0.010386  0.020600 -0.054271  ... -0.002410  0.000000   \n",
       "2017-12-03  0.073838 -0.028456  0.045796  0.024717  ...  0.065742  0.000000   \n",
       "2008-02-03  0.006245 -0.078058  0.036913  0.083217  ...  0.137066  0.127561   \n",
       "2008-01-27 -0.008984 -0.090807 -0.034771  0.054572  ...  0.018349 -0.026292   \n",
       "2008-01-20  0.015818 -0.019721 -0.015219 -0.044397  ...  0.040573  0.010999   \n",
       "2008-01-13 -0.052095  0.097385  0.080137 -0.017313  ... -0.054176 -0.047993   \n",
       "2008-01-06 -0.029478 -0.098374 -0.037363 -0.132733  ... -0.027290 -0.075806   \n",
       "\n",
       "ticker          ZINC      ZION      ZIOP      ZIXI       ZLC       ZMH  \\\n",
       "date                                                                     \n",
       "2017-12-31  0.000000 -0.009741  0.022222 -0.015730  0.000000  0.000000   \n",
       "2017-12-24  0.000000  0.026395 -0.068966 -0.024123  0.000000  0.000000   \n",
       "2017-12-17  0.000000 -0.018064 -0.018059  0.075472  0.000000  0.000000   \n",
       "2017-12-10  0.000000  0.016973 -0.015556 -0.055679  0.000000  0.000000   \n",
       "2017-12-03  0.000000  0.080475  0.014656 -0.006637  0.000000  0.000000   \n",
       "2008-02-03  0.286550  0.167722 -0.087879  0.069364  0.171949  0.193189   \n",
       "2008-01-27 -0.046975  0.136418 -0.003021  0.145695  0.042164 -0.014553   \n",
       "2008-01-20 -0.167109 -0.051614 -0.054286 -0.124638  0.037172 -0.037312   \n",
       "2008-01-13 -0.102381  0.037264 -0.022346 -0.172662  0.011799  0.051880   \n",
       "2008-01-06 -0.004739 -0.081058  0.101538 -0.143737 -0.134100  0.000752   \n",
       "\n",
       "ticker           ZQK      ZUMZ  \n",
       "date                            \n",
       "2017-12-31  0.000000 -0.029138  \n",
       "2017-12-24  0.000000  0.067164  \n",
       "2017-12-17  0.000000 -0.051887  \n",
       "2017-12-10  0.000000  0.062657  \n",
       "2017-12-03  0.000000  0.047244  \n",
       "2008-02-03  0.127811  0.149083  \n",
       "2008-01-27  0.141892  0.118666  \n",
       "2008-01-20 -0.030144 -0.076969  \n",
       "2008-01-13  0.018692 -0.094249  \n",
       "2008-01-06 -0.133102 -0.269012  \n",
       "\n",
       "[10 rows x 2489 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns.head().append(returns.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create & stack 52-week sequences"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll use 52-week sequences, which we'll create in a stacked format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "n = len(returns)\n",
    "T = 52 # weeks\n",
    "tcols = list(range(T))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 50 100 150 200 250 300 350 400 450 <class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1244500 entries, 0 to 1244499\n",
      "Data columns (total 25 columns):\n",
      "ticker    1244500 non-null object\n",
      "0         1244500 non-null float64\n",
      "1         1244500 non-null float64\n",
      "2         1244500 non-null float64\n",
      "3         1244500 non-null float64\n",
      "4         1244500 non-null float64\n",
      "5         1244500 non-null float64\n",
      "6         1244500 non-null float64\n",
      "7         1244500 non-null float64\n",
      "8         1244500 non-null float64\n",
      "9         1244500 non-null float64\n",
      "10        1244500 non-null float64\n",
      "11        1244500 non-null float64\n",
      "12        1244500 non-null float64\n",
      "13        1244500 non-null float64\n",
      "14        1244500 non-null float64\n",
      "15        1244500 non-null float64\n",
      "16        1244500 non-null float64\n",
      "17        1244500 non-null float64\n",
      "18        1244500 non-null float64\n",
      "19        1244500 non-null float64\n",
      "20        1244500 non-null float64\n",
      "21        1244500 non-null float64\n",
      "year      1244500 non-null int64\n",
      "month     1244500 non-null int64\n",
      "dtypes: float64(22), int64(2), object(1)\n",
      "memory usage: 237.4+ MB\n"
     ]
    }
   ],
   "source": [
    "data = pd.DataFrame()\n",
    "for i in range(n-T-1):\n",
    "    if i % 50 == 0:\n",
    "        print(i, end=' ', flush=True)\n",
    "    df = returns.iloc[i:i+T+1]\n",
    "    data = pd.concat([data, (df\n",
    "                             .reset_index(drop=True)\n",
    "                             .transpose()\n",
    "                             .reset_index()\n",
    "                             .assign(year=df.index[0].year,\n",
    "                                     month=df.index[0].month))],\n",
    "                     ignore_index=True)\n",
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create categorical variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create dummy variables for different time periods, namely months and years:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 1244500 entries, 2008-06-01 to 2017-12-01\n",
      "Data columns (total 45 columns):\n",
      "ticker       1244500 non-null int64\n",
      "1            1244500 non-null float64\n",
      "2            1244500 non-null float64\n",
      "3            1244500 non-null float64\n",
      "4            1244500 non-null float64\n",
      "5            1244500 non-null float64\n",
      "6            1244500 non-null float64\n",
      "7            1244500 non-null float64\n",
      "8            1244500 non-null float64\n",
      "9            1244500 non-null float64\n",
      "10           1244500 non-null float64\n",
      "11           1244500 non-null float64\n",
      "12           1244500 non-null float64\n",
      "13           1244500 non-null float64\n",
      "14           1244500 non-null float64\n",
      "15           1244500 non-null float64\n",
      "16           1244500 non-null float64\n",
      "17           1244500 non-null float64\n",
      "18           1244500 non-null float64\n",
      "19           1244500 non-null float64\n",
      "20           1244500 non-null float64\n",
      "21           1244500 non-null float64\n",
      "label        1244500 non-null int64\n",
      "year_2008    1244500 non-null uint8\n",
      "year_2009    1244500 non-null uint8\n",
      "year_2010    1244500 non-null uint8\n",
      "year_2011    1244500 non-null uint8\n",
      "year_2012    1244500 non-null uint8\n",
      "year_2013    1244500 non-null uint8\n",
      "year_2014    1244500 non-null uint8\n",
      "year_2015    1244500 non-null uint8\n",
      "year_2016    1244500 non-null uint8\n",
      "year_2017    1244500 non-null uint8\n",
      "month_1      1244500 non-null uint8\n",
      "month_2      1244500 non-null uint8\n",
      "month_3      1244500 non-null uint8\n",
      "month_4      1244500 non-null uint8\n",
      "month_5      1244500 non-null uint8\n",
      "month_6      1244500 non-null uint8\n",
      "month_7      1244500 non-null uint8\n",
      "month_8      1244500 non-null uint8\n",
      "month_9      1244500 non-null uint8\n",
      "month_10     1244500 non-null uint8\n",
      "month_11     1244500 non-null uint8\n",
      "month_12     1244500 non-null uint8\n",
      "dtypes: float64(21), int64(2), uint8(22)\n",
      "memory usage: 254.0 MB\n"
     ]
    }
   ],
   "source": [
    "data[tcols] = (data[tcols].apply(lambda x: x.clip(lower=x.quantile(.01),\n",
    "                                                  upper=x.quantile(.99))))\n",
    "data.ticker = pd.factorize(data.ticker)[0]\n",
    "data['label'] = (data[0] > 0).astype(int)\n",
    "data['date'] = pd.to_datetime(data.assign(day=1)[['year', 'month', 'day']])\n",
    "data = pd.get_dummies((data.drop(0, axis=1)\n",
    "                       .set_index('date')\n",
    "                       .apply(pd.to_numeric)),\n",
    "                      columns=['year', 'month']).sort_index()\n",
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_hdf('data.h5', 'returns_daily')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1244500, 45)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "282.222px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
